package cn.ruituo.dao;

import cn.ruituo.entity.Student;
import cn.ruituo.util.DBUtil;
import cn.ruituo.util.StrUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//CRUD
public class StudentDao {
    //只返回
    public int add(Student stu) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        //不完整
        String sql = "insert into s_student(sname,gradeid,enterdate)values(?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, stu.getSname());
            ps.setLong(2, stu.getGradeid());
            ps.setDate(3, stu.getEnterdate() == null ? null : new java.sql.Date(stu.getEnterdate().getTime()));
            return ps.executeUpdate();
        } catch (SQLException e) {
            //e.printStackTrace();
            DBUtil.handleException(e);
        } finally {
            DBUtil.close(null, ps, connection);
        }

        return 0;
    }

    public int update(Student stu) {
        return 0;
    }

    public int del(long stuno) {
        return 0;
    }

    //    public int del2(long gradeid){
//        return 0;
//    }
    //查询
    //指定学号，查询学生信息，没有，返回null
    public Student get(long stuno) {

        String sql = "select * from s_student where stuno=?";
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setLong(1, stuno);
            rs = ps.executeQuery();
            if (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setAddress(rs.getString("address"));


                return student;
            }
        } catch (SQLException e) {
            DBUtil.handleException(e);
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return null;

    }

    //查询多个
    //没有找到，返回空集合
    public List<Student> findALL() {
        List<Student> datas = new ArrayList<>();
        String sql = "select * from s_student order by stuno asc";
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                student.setEnterdate(rs.getDate("enterdate"));

                //...
                datas.add(student);//!!!!
            }
        } catch (SQLException e) {
            DBUtil.handleException(e);
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return datas;
    }

    //查询全部学生 人数
    public int findTotal() {
        String sql = "select count(stuno) as a from s_student";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int total = 0;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }

        } catch (SQLException e) {
            DBUtil.handleException(e);
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return total;
    }
    //分页
    //limit

    /**
     * @param pageNo   页号，第几页
     * @param pageSize 一页多少条
     * @param sname    模糊查找条件
     * @return
     */
    public List<Student> paging(int pageNo, int pageSize, String sname, String qq, String telephone, Date enterdateStart, Date enterDateEnd) {
        String sql = "select * from s_student where 1=1";
        List<Object> params = new ArrayList<>();
        if (!StrUtil.isBlank(sname)) {
            sql += " and sname like \"%\"?\"%\" ";
            params.add(sname);
        }
        if (!StrUtil.isBlank(qq)) {
            sql += " and qq like \"%\"?\"%\" ";
            params.add(qq);
        }
        if (!StrUtil.isBlank(telephone)) {
            sql += " and telephone like \"%\"?\"%\" ";
            params.add(telephone);
        }
        sql+=" order by stuno limit ?,?";
        int start = (pageNo - 1) * pageSize;
        System.out.println("sql="+sql+",params="+params);
        List<Student> datas = new ArrayList<>();
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            ps.setInt(params.size() + 1, start);
            ps.setInt(params.size() + 2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setSex(rs.getString("sex"));
                student.setAddress(rs.getString("address"));
                student.setTelephone(rs.getString("telephone"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                //...
                datas.add(student);//!!!!
            }
        } catch (SQLException e) {
            DBUtil.handleException(e);
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return datas;
    }
}
